﻿using System;
using System.IO;
using OpenExcel.OfficeOpenXml;
using OpenExcel.OfficeOpenXml.Style;

namespace OpenExcel_Examples
{
    public static class Example1
    {
        public static void Run()
        {
            Console.WriteLine("=== Running Example1 ===");
            string outputFile = Path.Combine(Program.OutputDirectory, "example1.xlsx");
            using (ExcelDocument doc = ExcelDocument.CreateWorkbook(outputFile))
            {
                ExcelWorksheet wsheet = doc.Workbook.Worksheets.Add("My Order");

                // You may get errors from styles unless they're defined
                // (styles are not mandatory). Important for new Excel files,
                // not a problem if modifying existing file made by MS Excel
                doc.EnsureStylesDefined();

                wsheet.Cells["A1"].Value = "Date";
                wsheet.Cells["B1"].Style.Border.BottomStyle = ExcelBorderStyleValues.Thick;
                wsheet.Cells["B1"].Value = DateTime.Now;

                wsheet.Columns[1].Width = 20;
                wsheet.Columns["B"].Width = 30;
                wsheet.Rows[1].Height = 22.5;

                wsheet.Cells["A3"].Value = "Item";
                wsheet.Cells["B3"].Value = "Price";
                wsheet.Cells["C3"].Value = "Qty";
                wsheet.Cells["D3"].Value = "Amount";

                wsheet.Cells["A4"].Value = "Tuna sandwich";
                wsheet.Cells["B4"].Value = 35.00;
                wsheet.Cells["C4"].Value = 2;

                // Create an Amount formula
                // It will be copied to the other line items in Example2
                wsheet.Cells["D4"].Formula.Text = "B4*C4";

                // Add Total label formula
                wsheet.Cells["A6"].Value = "TOTAL";
                wsheet.Cells["D6"].Formula.Text = "SUM(D4:D5)";

                // Add total in another sheet
                // Automatic updating of cross-sheet formula shown in Example2
                ExcelWorksheet wsheet2 = doc.Workbook.Worksheets.Add("Another Sheet");
                wsheet2.Cells["A1"].Value = "TOTAL in Order sheet";
                wsheet2.Cells["B1"].Formula.Text = "SUM('My Order'!D4:D5)";
                wsheet2.Columns["A"].Width = 40;

                // Header
                ExcelFont font = doc.CreateFont("Arial", 12);
                font.Bold = true;
                wsheet.Cells["A3"].Style.Font = font;
                // Set style by row and column
                // Note that setting cell style currently overrides the row/col style
                // and does not merge -- to improve in next version
                wsheet.Rows[1].Style = wsheet.Cells["A1"].Style;
                wsheet.Columns["A"].Style = wsheet.Cells["A1"].Style;

                //wsheet.Cells["A3"].Style.Border = new Border()
                //{
                //    BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin }
                //};
                wsheet.Cells["A3"].Style.Border.BottomStyle = ExcelBorderStyleValues.Thin;
                wsheet.Cells["B3"].Style = wsheet.Cells["A3"].Style;
                wsheet.Cells["C3"].Style = wsheet.Cells["A3"].Style;
                wsheet.Cells["D3"].Style = wsheet.Cells["A3"].Style;

                // Line item(s)
                wsheet.Cells["B4"].Style.NumberFormat.Format = @"_-[$£-809]* #,##0.00_-;\-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-";
                wsheet.Cells["D4"].Style = wsheet.Cells["B4"].Style;

                // Total section
                wsheet.Cells["A6"].Style.Fill.ForegroundColor = "FFFFFF00";
                wsheet.Cells["A6"].Style.Fill.BackgroundColor = "FFFF00FF";
                wsheet.Cells["A6"].Style.Font.Name = "Verdana";
                wsheet.Cells["D6"].Style.Border.TopStyle = ExcelBorderStyleValues.Double;
                wsheet.Cells["D6"].Style.Border.BottomStyle = ExcelBorderStyleValues.Thick;
            }
        }
    }
}
